﻿using System;
using System.Collections.Generic;
using System.Text;
using PPI.Entities.Distribution;
using PPI.Entities.Lookup;
using PPI.Mobile.Logic;
using System.Data;
using VMind.Global;

namespace PPI.Client.Logic.Distribution
{
    public class LTop
    {
        public static List<EXMissionStructures_PoleTopType> SelectAllIdName(Int32 fromId)
        {
            DataTable dt = WebConsumer.Distribution.WDistributionPoleTop.SelectAllIdName(fromId).Tables[0];
            List<EXMissionStructures_PoleTopType> lstTops = new List<EXMissionStructures_PoleTopType>();

            foreach (DataRow drTop in dt.Rows)
            {
                EXMissionStructures_PoleTopType top = new EXMissionStructures_PoleTopType();

                top.Id = ConvertHelper.ToInt32(drTop["Distributions_TopTypeId"]);
                top.Name = drTop["Name"].ToString();

                lstTops.Add(top);
            }

            return lstTops;
        }



        public static List<EXMissionStructures_PoleTopType> SelectAllIdName()
        {
            DataTable dt = WebConsumer.Distribution.WDistributionPoleTop.SelectAllIdName().Tables[0];
            List<EXMissionStructures_PoleTopType> lstTops = new List<EXMissionStructures_PoleTopType>();

            foreach (DataRow drTop in dt.Rows)
            {
                EXMissionStructures_PoleTopType top = new EXMissionStructures_PoleTopType();

                top.Id = ConvertHelper.ToInt32(drTop["Distributions_TopTypeId"]);
                top.Name = drTop["Name"].ToString();

                lstTops.Add(top);
            }

            return lstTops;
        }

        public static List<EXMissionStructures_PoleTopType> Save(List<EXMissionStructures_PoleTopType> tops, Int32 poleId)
        {
            return WebConsumer.Distribution.WDistributionPoleTop.Save(tops, poleId);
        }

        public static void UpdateData(string connectionString)
        {
            StringBuilder _query = new StringBuilder();
            _query.AppendLine("SELECT MAX(LookupValue) FROM tb_Lookups WHERE LookupTypeFk = 'Top'");

            DataSet maxTop = LMobileDatabase.ExecuteQueryDataset(_query.ToString(), connectionString);
            Int32 idMaxTop = Int32.MaxValue;

            if (maxTop != null && maxTop.Tables.Count > 0)
                idMaxTop = Convert.ToInt32(maxTop.Tables[0].Rows[0][0]);

            List<EXMissionStructures_PoleTopType> tops = SelectAllIdName(idMaxTop);

            foreach (EXMissionStructures_PoleTopType top in tops)
            {
                string query = "INSERT INTO tb_Lookups (LookupValue, LookupText, LookupTypeFk) VALUES ('" + top.Id + "', '" + top.Name + "', 'Top')";
                LMobileDatabase.ExecuteQuery(query, connectionString);
            }
        }

        public static void UpdateUploadedData(List<EXMissionStructures_PoleTopType> tops, Int32 poleId, string connectionString)
        {
            foreach (EXMissionStructures_PoleTopType top in tops)
            {
                StringBuilder _query = new StringBuilder();
                _query.AppendLine("UPDATE tb_TopsXPoles set TopXPoleServerID = " + top.ServerId + ", PoleServerID = " + poleId + ", UploadedDate = " +
                                  DateTime.Now.ToString("yyyy-MM-dd") + " where TopXPoleId =" + top.Id + " ");

                LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
            }
        }

        public static List<EXMissionStructures_PoleTopType> GetPoleTops(EDistributionPole distributionPole, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   TP.TopXPoleID, ");
            _sql.AppendLine("   TP.TopFk, ");
            _sql.AppendLine("   LKS.LookupText ");
            _sql.AppendLine("FROM tb_TopsXPoles TP ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = TP.TopFk AND LKS.LookupTypeFk = 'Top' ");
            _sql.AppendLine("WHERE TP.PoleFk = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EXMissionStructures_PoleTopType> lstTops = new List<EXMissionStructures_PoleTopType>();

            foreach (DataRow dr in dt.Rows)
            {
                EXMissionStructures_PoleTopType poleTop = new EXMissionStructures_PoleTopType();

                poleTop.Id = ConvertHelper.ToInt32(dr["TopXPoleID"]);
                poleTop.Key = dr["TopFk"].ToString();
                poleTop.Name = dr["LookupText"].ToString();

                lstTops.Add(poleTop);
            }

            return lstTops;
        }
    
        public static List<EXMissionStructures_PoleTopType> GetProjectPoleTops(EDistributionPole distributionPole, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   TP.TopXPoleID, ");
            _sql.AppendLine("   TP.TopFk, ");
            _sql.AppendLine("   LKS.LookupText ");
            _sql.AppendLine("FROM tb_TopsXPoles TP ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = TP.TopFk AND LKS.LookupTypeFk = 'Top' ");
            _sql.AppendLine("WHERE TP.PoleFk = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EXMissionStructures_PoleTopType> lstTops = new List<EXMissionStructures_PoleTopType>();

            foreach (DataRow dr in dt.Rows)
            {
                EXMissionStructures_PoleTopType poleTop = new EXMissionStructures_PoleTopType();

                poleTop.Id = ConvertHelper.ToInt32(dr["TopXPoleID"]);
                poleTop.Key = dr["TopFk"].ToString();
                poleTop.Name = dr["LookupText"].ToString();

                lstTops.Add(poleTop);
            }

            return lstTops;
        }
    }
}
